package net.tycmc.cty.ctyms.timejob.iems.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.configuration.ConfigurationException;

import com.tycmc.functions.util.DynamicReadConfig;

import net.tycmc.bulb.common.dao.IBaseDAO;
import net.tycmc.bulb.common.dao.ITransactionDAO;
import net.tycmc.bulb.common.util.DateUtil;
import net.tycmc.bulb.common.util.MapGetter;
import net.tycmc.cty.ctyms.timejob.function.jsonToExcel.JsonListMap;

/**
 * jsonToExcel处理类
 * @author jiyongtian
 *
 */
public class JsonToExcelService {
	//高频文件根目录
	private String highFilePath = null;
	//新增高频文件转换记录语句
	private List<String> parseHighSQL = new ArrayList<String>();
	//新增高频文件转换记录参数
	private List<Object[]> parseHighPar = new ArrayList<Object[]>();
	//新增sd文件转换记录语句
	private List<String> parseSDSQL = new ArrayList<String>();
	//新增sd文件转换记录参数
	private List<Object[]> parseSDPar = new ArrayList<Object[]>();
	private IBaseDAO baseDAO;
	private ITransactionDAO tranDAO;
	public String getHighFilePath() {
		return highFilePath;
	}
	public void setHighFilePath(String highFilePath) {
		this.highFilePath = highFilePath;
	}
    public IBaseDAO getBaseDAO() {
		return baseDAO;
	}
	public void setBaseDAO(IBaseDAO baseDAO) {
		this.baseDAO = baseDAO;
	}
	public ITransactionDAO getTranDAO() {
		return tranDAO;
	}
	public void setTranDAO(ITransactionDAO tranDAO) {
		this.tranDAO = tranDAO;
	}
	public JsonToExcelService(){
		   
	}

	public void startStat(){
		this.findFileUrl();
		this.parseHighSQL.clear();
		this.parseHighPar.clear();
		this.parseSDSQL.clear();
		this.parseSDPar.clear();
		System.out.println("开始转换高频采集结果");
		this.parseHighFreFile();
		System.out.println("转换高频采集结果结束");
		System.out.println("开始转换sd卡上传文件");
		this.parseUploadFileOfSDData();
		System.out.println("转换sd卡上传文件结束");
		if(this.parseHighSQL!=null&&this.parseHighSQL.size()>0&&this.parseHighSQL.size()==this.parseHighPar.size()){
			this.tranDAO.batch(this.parseHighSQL, this.parseHighPar);
		}
		if(this.parseSDSQL!=null&&this.parseSDSQL.size()>0&&this.parseSDSQL.size()==this.parseSDPar.size()){
			this.tranDAO.batch(this.parseSDSQL, this.parseSDPar);
		}
	}
	/**
	 * 高频文件根地址
	 **/
	private void findFileUrl() {
		String fileSrcPath = "";
		try {
			fileSrcPath = DynamicReadConfig.getConfigProperty("config.properties", "filePath");
			fileSrcPath = new String(fileSrcPath.getBytes("iso-8859-1"),"UTF-8");
		} catch (Exception e) {
//			e.printStackTrace();
			System.out.println("获取高频文件路径出错！");
		}
		this.setHighFilePath(fileSrcPath);
	}
	/**
	 * 高频文件jsontoExcel
	 **/
	private void parseHighFreFile(){
		File rootfile = new File(highFilePath);
		Map<String,Object> lastwork = this.getLastWorkDay();
		if(rootfile.exists()){
			List<File> list = new ArrayList<File>();
			/*第一次作业*/
			if(lastwork==null||lastwork.isEmpty()){
				list = this.getFilesByErgodicFolder(rootfile);
			}else{
				list = this.getFileByVerifyExists(MapGetter.getString(lastwork,"FileHG_WorkTime"));
			}
			for(File file:list){
				String filepath = file.getAbsolutePath();
				File excel = new File(filepath.substring(0,filepath.lastIndexOf("."))+".xlsx");
				if(excel.exists()){
					excel.delete();	
				}
				try {
					excel.createNewFile();
				} catch (IOException e) {
//					e.printStackTrace();
					System.out.println("高频采集结果文件没有创建权限");
					break;
				}
				/*
				if(this.parseFileInterface(file,excel)){
					this.insertIntoFileParseRecord(file,"1",null);
					this.insertIntoHighFileRecord(file);
				}else{
					if(excel.exists()){
						excel.delete();
					}
					System.out.println("转换高频文件"+filepath+"失败");
				}*/
				String[] times = null;
				try {
					times = this.parseFileInterface(file,excel);
				} catch (Exception e) {
					if(excel.exists()){
						excel.delete();
					}
					break;
//					e.printStackTrace();
				}
				String highId = this.insertIntoHighFileRecord(file,times);
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("FileHG_ID", highId);
				this.insertIntoFileParseRecord(file,"1",map);
			}
		}else{
			System.out.println("高频采集结果文件路径保存无法访问，请开放权限！");
		}
	}
	/**sd卡上传解析后的文件jsonToExcel*/
	private void parseUploadFileOfSDData() {
		String[] savePath = this.getSDFileAccessPath();
		List<Map<String,Object>> filesList = this.getSDFilesOfNeedParse();
		for(Map<String,Object> oneFileMap:filesList){
			File oneFile = new File(savePath[1]+MapGetter.getString(oneFileMap,"File_SaveName"));
			if(oneFile.exists()){
				String filePath = oneFile.getAbsolutePath();
				filePath = filePath.substring(0,filePath.lastIndexOf("."))+".xlsx";
				File excel = new File(filePath);
				if(excel.exists()){
					excel.delete();
				}
				try {
					excel.createNewFile();
				} catch (IOException e) {
//					e.printStackTrace();
					System.out.println("sd卡翻译文件保存目录没有创建权限");
					break;
				}
				/*
				if(this.parseFileInterface(oneFile,excel)){
					this.insertIntoFileParseRecord(oneFile,"2",oneFileMap);
				}else{
					if(excel.exists()){
						excel.delete();
					}
					System.out.println("转换sd卡文件"+oneFile.getAbsolutePath()+"失败");
				}*/
				try {
					this.parseFileInterface(oneFile,excel);
				} catch (Exception e) {
//					e.printStackTrace();
					if(excel.exists()){
						excel.delete();
					}
					System.out.println("转换sd卡文件"+oneFile.getAbsolutePath()+"失败");
					break;
				}
				this.insertIntoFileParseRecord(oneFile,"2",oneFileMap);
			}else{
				System.out.println("sd卡翻译文件保存目录没有访问权限！");
				break;
			}
		}
	}
	/**
	 * 新增文本文件转换excel文件记录
	 * @param File file
	 * @param String type
	 * 
	 **/
	public boolean insertIntoFileParseRecord(File file,String type,Map<String,Object> sdFile) {
		boolean flag = false;
		String fileName = file.getName();
		fileName = fileName.substring(0, fileName.lastIndexOf("."))+".xlsx";
		String strSQL ="insert into FileParseExcelRecord(FilePER_FileID,FilePER_FileName,FilePER_ParseTime,FilePER_FileType,"
		       +" FilePER_State)values(?,?,GETDATE(),?,?);";
		//高频文件
		if(type.equals("1")){
			this.parseHighSQL.add(strSQL);
			this.parseHighPar.add(new Object[]{MapGetter.getString(sdFile, "FileHG_ID"),fileName,type,"2"});
			flag = true;
		}else if(type.equals("2")){//sd文件	
			this.parseSDSQL.add(strSQL);
			this.parseSDPar.add(new Object[]{MapGetter.getString(sdFile,"File_FileID"),fileName,type,"2"});
			flag = true;
		}else{
			
		}
		return flag;
	}
	/**
	 * 调用文件文件转换excel接口
	 * @param File srcFile 文本文件
	 * @param File excel excel文件
	 * @throws Exception 
	 **/
	public String[] parseFileInterface(File srcFile, File excel) throws Exception{
//		boolean flag = false;
		String[] file = new String[]{srcFile.getAbsolutePath()};
		OutputStream os = null;
//		try {
			os = new FileOutputStream(excel);
//			JsonListMap.getInstance().jsonToExcel(file, os);
//			flag = true;
//		} catch (Exception e) {
//			if(excel.exists()){
//				excel.delete();
//			}
//		}finally{
//			if(os!=null){
//				try {
//					os.flush();
//					os.close();
//				} catch (IOException e) {
////					e.printStackTrace();
//				}
//			}
//		}
		return JsonListMap.getInstance().toExcelAndGetTimeBucket(file, os);
	}
	/**
	 * 作业首次工作，遍历文件夹获取需转换文件列表
	 * @param File rootFile 
	 *        根文件
	 * @return  List<File>      
	 **/
	private List<File> getFilesByErgodicFolder(File rootFile) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		String now = sdf.format(new Date());
		List<File> reFiles = new ArrayList<File>();
		//获取根文件夹的下一级文件夹，即设备号文件夹
		for(File folder:rootFile.listFiles()){
			if(folder.isDirectory()){
				//文本文件这一级
				for(File file:folder.listFiles()){
					if(file.isFile()){
						String fileName = file.getName();
						//如果不是今天的文件，进行校验是否合理，今天的文件不处理。
						if(fileName.indexOf(now)<0){
							if(checkFileName(fileName)){
								reFiles.add(file);
							}
						}
					}
				}
			}
		}
		return reFiles;
	}
	/**校验文件名合法性
	 * yyyyMMdd_设备号_1.txt
	 * @param String fileName 文件名 
	 **/
	public static boolean checkFileName(String fileName){
		boolean flag = false;
		String check="(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))_([a-zA-Z0-9\\-||\\u4e00-\\u9fa5_]+)_([0-5]).[t|T][x|X][t|T]";
		Pattern p1 = Pattern.compile(check);
		Matcher m1 = p1.matcher(fileName);   
		if(m1.matches()){
			flag = true;
		}
		return flag;
	}
	/**
	 * 获取高频采集文件
	 * @param  String  lastTime 上次统计最后一条信息
	 * @return  List<File> 
	 **/
	private List<File> getFileByVerifyExists(String  lastTime) {
		List<File> reFiles = new ArrayList<File>();
		List<Map<String,Object>> vclList = this.getValidVcls(null);
		String startDate = lastTime;
		String endDate = DateUtil.addDay(-1);
		SimpleDateFormat sDF = new SimpleDateFormat("yyyy-MM-dd");
		String baseSrcPath = highFilePath;
		for(int i=0;i<vclList.size();i++){
			Calendar sCalendar = Calendar.getInstance();
			Calendar eCalendar = Calendar.getInstance();
			try {
				sCalendar.setTime(sDF.parse(startDate));
				eCalendar.setTime(sDF.parse(endDate));
			} catch (ParseException e) {
				System.out.println("文件文件转换excel，获取日期异常");
				break;
//				e.printStackTrace();
			}
			String oneVcl = MapGetter.getString(vclList.get(i), "VehicleNumber");
			String oneVclPath = baseSrcPath + oneVcl + "\\";
			while(sCalendar.getTime().getTime()<= eCalendar.getTime().getTime()){
				for(int j=0;j<=5;j++){
					String fileName = sDF.format(sCalendar.getTime()).replace("-", "").trim()+"_"+oneVcl+"_"+String.valueOf(j)+".txt";
					File file = new File(oneVclPath+fileName);
					if(file.exists()){
						reFiles.add(file);
					}else{
						
					}
				}
				sCalendar.add(Calendar.DAY_OF_YEAR, 1);
			}	
		}
		return reFiles;
	}
	/**
	 * 新增高频文件记录
	 **/
	private String insertIntoHighFileRecord(File file,String[] times) {
		String fileid="";
//		String vclNum = file.getName().substring(9,file.getName().lastIndexOf("."));
		String vclNum = file.getName().split("_")[1];
		List<Map<String,Object>> list = this.getValidVcls(vclNum);
		String Vclid = MapGetter.getString(list.get(0), "VehicleID");
		String sql = "insert into FileOfHighGather(FileHG_VclID,FileHG_FileName,FileHG_ModifiedTime," +
				"FileHG_StartTime,FileHG_EndTime,FileHG_WorkTime)values(?,?,?,?,?,GETDATE());select @@identity as FileHG_ID";
		Calendar ca = Calendar.getInstance();
		ca.setTimeInMillis(file.lastModified());
		List<Map<String,Object>> filehigh = this.baseDAO.execQuery(sql, new Object[]{Vclid,file.getName(),ca.getTime(),times[0],times[1]});
		if(filehigh!=null&&filehigh.size()>0){
			fileid = MapGetter.getString(filehigh.get(0), "FileHG_ID");
		}
		return fileid;
	}
	/**SD卡文件服务器访问路径
	 * @return String[] filePath
	 *         filePath[0] 上传文件保存路径
	 *         filePath[1] 解析文件保存路径
	 */
	public String[] getSDFileAccessPath() {
		 List<Map<String,Object>> srcpath=this.getPathOfSaveSDFile();
		 String[] filePath = null;
		 if(srcpath!=null&&srcpath.size()==2){
			 filePath = new String[2];
			 for(int i=0;i<srcpath.size();i++){
				 String savePathIP = null;
				 String savePathRepath = null;
				 savePathIP = MapGetter.getString(srcpath.get(i), "Sys_Url_IP");
				 savePathRepath = MapGetter.getString(srcpath.get(i), "Sys_Url_Repath");
				 if((savePathRepath.indexOf(":")>=0)){
					 savePathRepath = savePathRepath.substring(savePathRepath.lastIndexOf(":")+1);
				 }
				 if((savePathRepath.lastIndexOf("\\")>=0)){
					 if(savePathRepath.lastIndexOf("\\")!= savePathRepath.length()){
						 savePathRepath = savePathRepath.substring(savePathRepath.lastIndexOf("\\")+1)+"\\"; 
					 }else{
						 savePathRepath = savePathRepath.substring(0,savePathRepath.length()-2);
						 savePathRepath = savePathRepath.substring(savePathRepath.lastIndexOf("\\")+1)+"\\";
					 }
				 }
				 if(MapGetter.getString(srcpath.get(i),"Sys_Url_Type").equals("0")){
					 filePath[0] = "\\\\"+savePathIP+"\\"+savePathRepath;
				 }else if(MapGetter.getString(srcpath.get(i),"Sys_Url_Type").equals("1")){
					 filePath[1] = "\\\\"+savePathIP+"\\"+savePathRepath;
				 }else{
					 
				 }
			 }
		 }
		 return filePath;
	}
	/**
	 * 查询需要转换为Excel文件的SD卡文件信息
	 * 
	 **/
	private List<Map<String, Object>> getSDFilesOfNeedParse() {
		StringBuffer strBuf = new StringBuffer();
		strBuf.append("select sd.*  from FileOprRecord sd inner join V_VehicleInfo vv on sd.File_VclID = vv.VehicleID ");
		strBuf.append("where vv.UsefulLife>=GETDATE() and Tmnl_SoftEdition is not null and Tmnl_SoftEdition!='' ");
		strBuf.append("and File_ResolvingTime is not null and File_ResolvingTime!='' and sd.File_FileID  not in (select ");
		strBuf.append("FilePER_FileID from FileParseExcelRecord where FilePER_FileType =2 and FilePER_State =2);");
		return this.baseDAO.execQuery(strBuf.toString(), new Object[]{});
	}
	/**
     * 查询SD卡文件服务器的访问路径
     * Sys_Url_Type = 0时 保存路径
     * Sys_Url_Type = 1时 解释程序的保存路径
     **/
	private List<Map<String,Object>> getPathOfSaveSDFile() {
		String SQL = "select Sys_Url_Type,Sys_Url_IP,Sys_Url_Repath from Sys_FileURL where Sys_Url_Type in (0,1) order by Sys_Url_Type;";
		return this.baseDAO.execQuery(SQL, new Object[]{});
	}
	/**获取上次统计高频采集文件的最后一条记录*/
	private Map<String,Object> getLastWorkDay() {
		String sql = "select top 1 CONVERT(varchar ,FileHG_WorkTime,120) as FileHG_WorkTime from FileOfHighGather fh "+
		             " left join V_VehicleInfo vv on  vv.VehicleID = fh.FileHG_VclID where vv.UsefulLife >= GETDATE() " + 
		             " and vv.Tmnl_SoftEdition is not null and vv.Tmnl_SoftEdition<>'' order by FileHG_WorkTime desc;";
		return this.baseDAO.execQueryOne(sql, new Object[]{});
	}
	/**获取有效设备列表*/
	private List<Map<String, Object>> getValidVcls(String vclNum) {
		StringBuffer strSQL = new StringBuffer();
		strSQL.append("select * from V_VehicleInfo where 1=1 ");
		if(vclNum!= null && vclNum.length()>0){
			strSQL.append(" and VehicleNumber like '"+vclNum+"' ");
		}
		strSQL.append("and UsefulLife>=GETDATE() and Tmnl_SoftEdition is not null and Tmnl_SoftEdition!='';");
		return this.baseDAO.execQuery(strSQL.toString(), new Object[]{});
	}
	public static void main(String[] args) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		System.out.println(sdf.format(new Date()));
//		System.out.println(JsonToExcelService.checkFileName(fileName));
	}
}
